package com.iris.live.services.data.repositories.impl;

import com.google.common.collect.Maps;
import com.iris.live.services.data.repositories.MessageRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.services.MessageService;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by Huang Yongping on 2016/9/17.
 */

@Repository
@Transactional
public class MessageRepositoryImpl extends BaseRepository implements MessageRepository {


    @Override
    public PageData getClientInfoByTel(MessageService.SaveActivityInviteRequest req, String permit) {
        String sql = " select a.*,COUNT(DISTINCT a.顾客姓名) from (   " +
                " select a.*, h.车型 as 试驾车型 from \n" +
                "(\n" +
                "select a.*, h.车型 as 意向车型 from \n" +
                "(\n" +
                "SELECT \n" +
                "a.`顾客姓名`,\n" +
                "a.`电话号码`,\n" +
                " c.`人员姓名`  销售顾问,\n" +
                "b.进店日期,\n" +
                "a.`意向车型A` ,\n" +
                "d.`试驾车型编号`,\n" +
                "b.到店目的 进店目的,\n" +
                "a.原顾客级别 意向级别,\n" +
                " b.接待备注,\n" +
                "if(a.是否订单='否', '',h.车型 )  as 订单车型,\n" +
                "if(a.是否交车='否','',h.车型) as 交车\n" +
                "from 顾客记录汇总 a\n" +
                " LEFT JOIN `顾客进店记录表` b on a.顾客编号=b.顾客编号\n" +
                "LEFT JOIN 顾客试驾记录 d on d.进店编号=b.进店编号\n" +
                "LEFT join 用户 c on  a.`所属销售顾问`=c.用户名\n" +
                "LEFT join  " +
                "  (SELECT b.顾客编号, \n" +
                "if(b.`库存编号` is null, b.车型编号,\n" +
                "   (select d.车型编号 from lsh车型 d, lsh库存表 e \n" +
                "where e.库存编号 = b.库存编号 and e.车型编号 = d.车型编号)) 车型编号\n" +
                "from  报价单表 b\n" +
                "where b.是否还原点='是'\n" +
                "AND b.是否驳回='否')    " +
                " e on a.顾客编号=e.顾客编号\n" +
                " LEFT join lsh车型 h on h.车型编号=e.车型编号   \n" +
                " where  1=1   \n";


        if (permit != null) {
            sql += " and a.许可='" + permit + "'  \n";
        } else {
            sql += " and 1=1 ";
        }

        if (req.电话号码 != null && !"".equals(req.电话号码)) {
            sql += " and  a.电话号码 like '%" + req.电话号码 + "%'  ";
        } else {
            sql += " and 1=1";
        }

        sql += ") a \n" +
                " LEFT join lsh车型 h on h.车型编号=a.意向车型A   \n" +
                ") a LEFT JOIN lsh车型 h on h.车型编号=a.试驾车型编号 " +
                "    ) a GROUP BY a.顾客姓名  ";
        return this.getPage(sql, req.getPageSize(), req.getPageIndex());


    }





    @Override
    public List getLatestAppointmentMsg(Integer subNum, String permit) {
        String sql = "SELECT 结束时间 预约时间,销售顾问,更新时间,更新账号 \n" +
                " FROM LSH待办事项\n" +
                " WHERE 附属编号 =:subNum AND 附属编号类型='线索' AND 是否删除='否' AND 许可=:permit \n" +
                " ORDER BY 更新时间 DESC LIMIT 1 ";
        Map map = Maps.newHashMap();
        map.put("subNum", subNum);
        map.put("permit", permit);
        return this.getList(sql,map);
    }

    @Override
    public List getLshMessageByIdAndPermit(String subStringInfo, String permit) {
        String sql = "SELECT a.接收人 AS 账号,b.人员姓名 AS 姓名,a.消息内容,a.消息编号,b.用户名,a.更新账号,a.更新时间 \n" +
                "FROM LSH消息 a \n" +
                "INNER JOIN 用户 AS b ON a.接收许可 = b.许可 \n" +
                "WHERE a.消息附属ID =:subStringInfo AND a.附属ID类型 IS NULL AND a.接收许可 =:permit AND a.接收人 = b.用户名 AND a.是否删除 = '否' \n" +
                "ORDER BY a.更新时间 DESC";
        Map map = Maps.newHashMap();
        map.put("subStringInfo", subStringInfo);
        map.put("permit", permit);
        return this.getList(sql, map);
    }

    @Override
    public List isAvailableClue(String phone, String permit) {
        String sql = "select a.导入编号,a.活动编号,a.许可,a.顾客姓名,a.电话号码 " +
                "from LSH导入名单 a where a.电话号码=:phone and a.许可=:permit  " +
                "and a.是否有效线索='是' and (a.线索创建日期 >= SUBDATE(CURDATE(),INTERVAL 3 month) AND a.线索创建日期 <=NOW()) ";
        Map map = Maps.newHashMap();
        map.put("phone", phone);
        map.put("permit", permit);
        List data = this.getList(sql, map);
        return data;
    }

    @Override
    public List getFollowingCustomer(String permit, String cdo) {
        String sql = "SELECT 顾客姓名,IF(顾客性别='男','先生','女士') 称谓,电话号码,下次跟进日期 下次跟进时间 \n" +
                " FROM `LSH导入名单` WHERE `下次跟进日期` >= :startTime AND `下次跟进日期` < :endTime AND 许可 =:permit \n" +
                " AND (所属销售顾问 IS NULL OR 所属销售顾问 = '') AND 所属CDO =:cdo AND (战败去向 IS NULL OR 战败去向= '') AND 是否有效线索 = '是'";
        Map map = Maps.newHashMap();
        map.put("permit", permit);
        map.put("cdo", cdo);
        String startTime = new SimpleDateFormat("yyyy-MM-dd 00:00:00").format(new Date());
        Calendar c = Calendar.getInstance();
        c.add(Calendar.DAY_OF_MONTH, 1);
        String endTime = new SimpleDateFormat("yyyy-MM-dd 00:00:00").format(c.getTime());
        map.put("startTime", startTime);
        map.put("endTime", endTime);
        List list = this.getList(sql, map);
        return list;
    }

    public List getActivityInviteList(String permit, String code) {
        String sql = "SELECT \ta.`顾客姓名`, \ta.`电话号码` 电话, \ta.`顾客性别`, \ta.`客户级别` 意向级别, \tc.`人员姓名` 销售顾问, a.`导入编号` \n" +
                " FROM \t`LSH导入名单` AS a LEFT JOIN `用户` AS c ON c.`用户名` = a.`所属销售顾问` \n" +
                " WHERE a.`活动编号` =:code AND a.`许可` =:permit AND a.是否线索='是' and (a.导入状态 = 0 or a.导入状态 = 1) GROUP BY a.`电话号码`";
        Map map = Maps.newHashMap();
        map.put("permit", permit);
        map.put("code", code);
        return this.getList(sql, map);
    }

//    @Override
//    public List getActivityBeforInviteListFromImport(String permit, String phone,String name) {
//        String sql = "SELECT  \n" +
//                "  :name as 顾客姓名,   \n" +
//                "  b.电话号码,  \n" +
//                "  if(b.顾客性别 = '', null, b.顾客性别) AS 顾客性别,   \n" +
//                "  b.渠道,   \n" +
//                "  b.来源,  \n" +
//                "  '是' as 是否存在,   \n" +
//                "  b.客户级别,   \n" +
//                "  '1' as 表来源,   \n" +
//                "  b.所属CDO,  \n" +
//                "  c.人员姓名 as 所属销售顾问  \n" +
//                "FROM LSH导入名单 AS b  \n" +
//                "LEFT JOIN 用户 c ON c.用户名 = b.所属销售顾问  \n" +
//                "WHERE b.电话号码 = :phone   \n" +
//                "AND b.是否有效线索 = '是'   \n" +
//                "AND b.许可 = :permit   \n" +
//                "ORDER BY b.更新时间 DESC  \n" +
//                "LIMIT 1  \n";
//        Map map=Maps.newHashMap();
//        map.put("permit",permit);
//        map.put("phone",phone);
//        map.put("name",name);
//        return this.getList(sql,map);
//    }

//    @Override
//    public List getActivityBeforInviteListFromRecords(String permit, String phone,String name) {
//        String sql = "SELECT  \n" +
//                "  :name as 顾客姓名,  \n" +
//                "  a.电话号码,  \n" +
//                "  if(a.顾客性别 = '', null, a.顾客性别) AS 顾客性别,   \n" +
//                "  a.顾客渠道 as 渠道,   \n" +
//                "  a.顾客来源 as 来源,   \n" +
//                "  a.新顾客级别 as 客户级别,   \n" +
//                "  '0' 表来源,   \n" +
//                "  '' 所属CDO ,  \n" +
//                "  c.人员姓名 as 所属销售顾问  \n" +
//                "FROM 顾客记录汇总 AS a  \n" +
//                "LEFT JOIN 用户 c ON c.用户名 = a.所属销售顾问  \n" +
//                "WHERE a.电话号码 = :phone   \n" +
//                "AND a.许可 = :permit   \n" +
//                "ORDER BY a.更新时间 DESC   \n" +
//                "LIMIT 1  \n";
//        Map map=Maps.newHashMap();
//        map.put("permit",permit);
//        map.put("phone",phone);
//        map.put("name",name);
//        return this.getList(sql,map);
//    }

    @Override
    public List getActivityAfterInviteListFromImport(String permit, String phone,String name,String saleMan) {
        String sql="SELECT :name as 顾客姓名,:saleMan as 销售顾问, b.`电话号码`, b.`顾客性别`, b.`渠道`, b.`来源`, b.`客户级别`, b.`所属销售顾问`," +
                " d.`人员姓名` `原销售顾问`,'是' as 销售顾问是否存在, '1' `表来源`, b.`所属CDO` FROM `LSH导入名单` AS b INNER JOIN `用户` AS d " +
                " ON d.`许可` = b.`许可` AND d.`用户名` = b.`所属销售顾问` WHERE b.`电话号码` =:phone AND  b.是否有效线索 = '是' AND b.`许可` =:permit ORDER BY b.`更新时间` DESC LIMIT 1";
        Map map=Maps.newHashMap();
        map.put("permit",permit);
        map.put("phone",phone);
        map.put("name",name);
        map.put("saleMan",saleMan);
        return this.getList(sql,map);
    }

    @Override
    public List getActivityAfterInviteListFromRecords(String permit, String phone,String name,String saleman) {
        String sql="SELECT :name as 顾客姓名,:saleman as 销售顾问, a.`电话号码`, a.`顾客性别`, a.`顾客渠道` `渠道`, a.`顾客来源` `来源`, a.`新顾客级别` `客户级别`," +
                " a.`所属销售顾问`,'是' as  销售顾问是否存在,c.`人员姓名` `原销售顾问`, '0' `表来源`, '' `所属CDO` " +
                " FROM `顾客记录汇总` AS a INNER JOIN `用户` AS c ON c.`许可` = a.`许可` " +
                " AND c.`用户名` = a.`所属销售顾问` WHERE a.`电话号码` =:phone AND a.`许可` =:permit ORDER BY a.`更新时间` DESC LIMIT 1 ";
        Map map=Maps.newHashMap();
        map.put("permit",permit);
        map.put("phone",phone);
        map.put("name",name);
        map.put("saleman",saleman);
        return this.getList(sql,map);
    }

    @Override
    public List getLshActivitySet() {
        String sql="select 名称,创建时间,更新时间,if(启用状态 = '启用','启用','停用') 启用状态," +
                "  if(`启用状态` = '启用','停用','启用') 操作,活动发起编号,更新账号 " +
                "  FROM `LSH活动发起设置` ORDER BY 更新时间 desc";
        return this.getList(sql);
    }

    @Override
    public List getClientInfoByTelOverWrite(String permit, String tel) {
        String sql = " SELECT " +
                " 	a.`顾客姓名` 客户姓名, " +
                " 	d.`进店日期`, " +
                " 	b.`人员姓名` 销售顾问, " +
                " 	a.`电话号码` 客户电话, " +
                " 	c.`value` 销售状态, " +
                " 	a.`顾客渠道` 渠道, " +
                " 	f.`车型` 意向车型, " +
                " 	d.`到店目的`, " +
                " 	a.`新顾客级别` 意向级别, " +
                " 	g.`车型` 试驾车型, " +
                " 	d.`接待备注`, " +
                " 	h.`车型` 订单车型, " +
                " 	h.`车型` 交车 " +
                " FROM ( " +
                " 	SELECT " +
                " 		a.`顾客编号`, " +
                " 		a.`许可`, " +
                " 		a.`顾客姓名`, " +
                " 		a.`电话号码`, " +
                " 		a.`顾客渠道`, " +
                " 		a.`意向车型A`, " +
                " 		a.`所属销售顾问`, " +
                " 		a.`新顾客级别`, " +
                " 		a.`新车销售状态` " +
                " 	FROM `顾客记录汇总` a " +
                " 	WHERE a.`许可` = :permit " +
                "   and a.电话号码 = :tel " +
                " 	UNION ALL " +
                " 	SELECT " +
                " 		'' 顾客编号, " +
                " 		b.`许可`, " +
                " 		b.`顾客姓名`, " +
                " 		b.`电话号码`, " +
                " 		b.`渠道` 顾客渠道, " +
                " 		b.`车型编号` 意向车型A, " +
                " 		b.`所属销售顾问`, " +
                " 		b.`客户级别` 新顾客级别, " +
                " 		b.`新车销售状态` " +
                " 	FROM `lsh导入名单` b " +
                " 	WHERE b.`许可` = :permit " +
                "   and b.电话号码 = :tel " +
                " 	and b.`是否有效线索` = '是' " +
                " 	AND b.`是否线索` = '是' " +
                " 	AND b.`导入日期` >= DATE_SUB(CURDATE(),INTERVAL 3 MONTH) " +
                " 	AND b.`电话号码` NOT IN ( " +
                " 		SELECT " +
                " 			c.`电话号码` " +
                " 		FROM `顾客记录汇总` c " +
                " 		WHERE c.`许可` = :permit " +
                " 	) " +
                " ) a " +
                " LEFT JOIN `用户` b ON b.用户名 = a.`所属销售顾问` " +
                " LEFT JOIN car_sales_state c ON c.`key` = a.`新车销售状态` " +
                " LEFT JOIN ( " +
                " 	SELECT  " +
                " 		a.`顾客编号`, " +
                " 		a.`进店编号`, " +
                " 		a.`进店日期`, " +
                " 		a.`到店目的`, " +
                " 		a.`接待备注` " +
                " 	FROM `顾客进店记录表` a " +
                " 	JOIN ( " +
                " 		SELECT " +
                " 			MAX(a.`进店编号`) 进店编号 " +
                " 		from `顾客进店记录表` a " +
                " 		WHERE a.`许可` = :permit " +
                " 		GROUP BY a.`顾客编号` " +
                " 	) b ON a.`进店编号` = b.`进店编号` " +
                " ) d ON a.`顾客编号` = d.`顾客编号` " +
                " LEFT JOIN `顾客试驾记录` e ON d.`进店编号` = e.`进店编号` " +
                " LEFT JOIN lsh车型 f ON a.`意向车型A` = f.`车型编号` " +
                " LEFT JOIN lsh车型 g ON e.试驾车型编号 = g.`车型编号` " +
                " LEFT JOIN ( " +
                " 	SELECT  " +
                " 		a.`顾客编号`, " +
                " 		a.车型编号, " +
                " 		c.车型 " +
                " 	FROM `报价单表` a " +
                " 	JOIN ( " +
                " 		SELECT " +
                " 			MAX(a.`报价编号`) 报价编号 " +
                " 		from `报价单表` a  " +
                " 		WHERE a.许可 = :permit  " +
                " 		AND a.`是否还原点` = '是'  " +
                " 		AND a.是否驳回 = '否' " +
                " 		GROUP BY a.`顾客编号` " +
                " 	) b ON a.`报价编号` = b.`报价编号` " +
                " 	LEFT JOIN lsh车型 c ON a.车型编号 = c.车型编号 " +
                " ) h ON h.顾客编号 = a.顾客编号 " +
                " group by a.电话号码";

        Map param = new HashMap();
        param.put("permit", permit);
        param.put("tel", tel);
        return this.getList(sql, param);


    }
}
